/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package BD.Modulos;
import BD.Bd;
import BD.ConnBdType;
import Interfaces.BdException;
import Interfaces.PlacesInterface;
import Objects.Parish;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 *
 * @author ARMGARCES
 */
public class BdLugares extends Bd{        
    /**
     * 
     */
    public BdLugares(){
       
    }   
    /**
     * 
     * @param gi
     * @return 
     */
    private String selectLoadSP(PlacesInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "Country":
                //params = new String[]{"@numguia","@producto" };
                return "{call sp_get_country_all}";
            case "State":
                //params = new String[]{"@numguia","@almacen" };
                return "{call sp_get_state_all}";
            case "City":
                //params = new String[]{"@numguia","@producto" };
                return "{call sp_get_city_all}";
            case "Municipality":
                //params = new String[]{"@numguia","@almacen" };
                return "{call sp_get_municipality_all}";   
            case "Parish":
                //params = new String[]{"@numguia","@almacen" };
                return "{call sp_get_parish_all}"; 
            default:
                return "";
        }
    }
    /**
     * 
     * @param value
     * @return 
     */
    private String selectFindSP(PlacesInterface value){
        System.out.println("Class = " + value.getClass().getSimpleName());
        switch(value.getClass().getSimpleName()){
            case "State":
                params = new String[]{"@country"};
                return "{call sp_get_state_find(?)}";
            case "City":
                params = new String[]{"@state"};
                return "{call sp_get_city_find(?)}";
            case "Municipality":
                params = new String[]{"@municipality"};
                return "{call sp_get_municipality_find(?)}";
            case "Parish":
                params = new String[]{"@municipality","@state"};
                return "{call sp_get_parish_find(?,?)}";
            default:
                return "";
        }
    }   
    /***************************************************************************/
    /********************************** LUGARES ********************************/
    /***************************************************************************/
    /**
     * 
     * @param value
     * @return
     * @throws SQLException
     * @throws BdException 
     */
    public PlacesInterface[] load(PlacesInterface value) throws SQLException, BdException {
        try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectLoadSP(value));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
                ResultSet result = cstmt.executeQuery();            

                List<PlacesInterface> list = new ArrayList<>();
                while(result.next()) {
                   PlacesInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new PlacesInterface[list.size()]);               
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }
    /**
     * 
     * @param value
     * @param find
     * @param findS
     * @return
     * @throws SQLException
     * @throws BdException 
     */
    public PlacesInterface[] find(PlacesInterface value,String find,String findS) throws SQLException, BdException {
         try{
            Bd bd = ConnBdType.open(ConnBdType.SqlServer);
            connection = bd.open();
            if (connection != null){
                StringBuilder sqlProc = new StringBuilder();
                sqlProc.append(selectFindSP(value));
                CallableStatement cstmt = connection.prepareCall(sqlProc.toString());
                cstmt.setString(params[0] , find );
                if(value instanceof Parish)
                    cstmt.setString(params[1] , findS );
                ResultSet result = cstmt.executeQuery();                           
                List<PlacesInterface> list = new ArrayList<>();
                while(result.next()) {
                   PlacesInterface obj = value.getClass().newInstance();
                   obj.loadResultSet(result);
                   list.add(obj);
                }               
                return list.toArray(new PlacesInterface[list.size()]);
            }else{
                throw new BdException("Error: Connexion no activa");
            }
        }catch(IllegalAccessException | InstantiationException ie){            
            throw new BdException("Error: Problemas de Instacia!");
        } catch (SQLException e) {
            throw new BdException("Error en BD: "+e);
        }finally{
            connection.close();
        }
    }    
    /***************************************************************************/
    /******************************* SINGLETON *********************************/
    /***************************************************************************/    
    private static class BdHolder {
        private static final BdLugares INSTANCE = new BdLugares();
    }
    public static BdLugares getInstance() {
        return BdLugares.BdHolder.INSTANCE;
    }
}
